-- This demo serves to demo derived tables, CTEs, temp tables and table 
-- variables.
-- © Erland Sommarskog 2020.

USE SQLTitbits
go
-- A query from the UNION script. A derived table is a query that is used as 
-- a source for FROM. Try to run it without selecting the part "AS u". What 
-- happens?
SELECT DISTINCT Name, TransDate
FROM  (SELECT Name, TransDate
       FROM   dbo.EastTransactions
       UNION  ALL
       SELECT Name, TransDate
       FROM   dbo.WestTransactions) AS u;

-- Move over to another database, which has a big order table.
USE Northgale;
go
-- Return information about orders on a given day. The order ID, some customer
-- data and the amount. The amount has to be computed from the details table
-- Logically, the derived table computes the sum for all orders in the table, 
-- but when you run the full query, does it feel like it is processing one 
-- million orders?
SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   (SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
        FROM   dbo.[Order Details] OD
        GROUP  BY OD.OrderID) AS OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;
go
-- The same query, but with a CTE. The main difference is that the CTE has
-- a name. How fast does it run?
; WITH OrderAmounts AS (
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID
)
SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;
go
-- Here we are using a temp table for the same query. What is the
-- execution time here? This time the sum *is* computed for all orders.
DROP TABLE IF EXISTS #OrderAmounts;
CREATE TABLE #OrderAmounts (OrderID int           NOT NULL PRIMARY KEY,
                            Amount  decimal(20,2) NOT NULL);

INSERT #OrderAmounts (OrderID, Amount)
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID;

SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   #OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;
go
-- And here we use a table variable. How is the execution time this time?
-- Beware that the query may keep on running even if some data has been 
-- returned. (Note: on SQL 2019 you may not notice any difference.)
DECLARE @OrderAmounts TABLE (OrderID int           NOT NULL PRIMARY KEY,
                             Amount  decimal(20,2) NOT NULL);

INSERT @OrderAmounts (OrderID, Amount)
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID;

SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   @OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;
